﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace KaoWuYou.Models
{
    public class QuestionModel
    {
        [Required]
        public string AnswerKey
        { set; get; }
        [Required]
        public string Genre
        { set; get; }
        public List<string> Options
        { set; get; }
        public int QuestionID
        { set; get; }
        public List<QuestionModel> QuestionList
        { set; get; }
        [Required]
        public string Stem
        { set; get; }
        [Required]
        public int SubclassID
        { set; get; }
        [Required]
        public int SubjectID
        { set; get; }

        public bool DeleteQuestion()
        {
            try
            {
                SqlConnection conn = new SqlConnection(Systems.ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM Questions WHERE QuestionID = @QuestionID";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@QuestionID", QuestionID) 
            });
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void FindAllQuestions()
        {
            this.QuestionList = new List<QuestionModel>();

            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Questions";
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.QuestionList.Add(tmp);
            }
            sdr.Close();
            conn.Close();

        }

        public void FindQuestionsByGenre()
        {
            this.QuestionList = new List<QuestionModel>();
            
            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Questions WHERE Genre = @Genre";
            cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Genre", Genre) 
            });
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.QuestionList.Add(tmp);
            }

            sdr.Close();
            conn.Close();
        }

        public void FindQuestionsByGenreAndSubject()
        {
            this.QuestionList = new List<QuestionModel>();

            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Questions WHERE (Genre = @Genre) AND (SubjectID = @SubjectID)";
            cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Genre", Genre) ,
                new SqlParameter("@SubjectID",SubjectID)
            });
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.QuestionList.Add(tmp);
            }

            sdr.Close();
            conn.Close();
        }

        public void FindQuestionsByGenreAndSubjectAndSubclass()
        {
            this.QuestionList = new List<QuestionModel>();

            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Questions WHERE (Genre = @Genre) AND (SubjectID = @SubjectID) AND (SubClassID = @SubClassID)";
            cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Genre", Genre) ,
                new SqlParameter("@SubjectID",SubjectID),
                new SqlParameter("@SubClassID",SubclassID)
            });
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.QuestionList.Add(tmp);
            }

            sdr.Close();
            conn.Close();
        }

        public void FindQuestionsBySubject()
        {
            this.QuestionList = new List<QuestionModel>();

            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Questions WHERE SubjectID = @SubjectID";
            cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@SubjectID", SubjectID) 
            });
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.QuestionList.Add(tmp);
            }
            sdr.Close();
            conn.Close();
        }

        public void FindQuestionsBySubjectAndSubclass()
        {
            this.QuestionList = new List<QuestionModel>();

            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Questions WHERE (SubClassID = @SubClassID) AND (SubjectID = @SubjectID)";
            cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@SubClassID", SubclassID) ,
                new SqlParameter("@SubjectID",SubjectID)
            });
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.QuestionList.Add(tmp);
            }

            sdr.Close();
            conn.Close();
        }

        public void ImportQuestions(string questionFilePath)
        {
            throw new NotImplementedException();
        }

        public void ParseStemAndOptions()
        {
            throw new NotImplementedException();
        }
    }
}